package com.ztjava.core;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.ztjava.core.log.Log;

public class TableBulider {
	private final static Log log = Log.getLog(TableBulider.class);
	
	public static void bulderTable(DataSource dataSource){
		Connection conn = null;
		ResultSet rs = null;
		Statement stm = null;
		try {
			conn = dataSource.getConnection();
			stm = conn.createStatement();
			for(String tableName:Constant.pluginTable.keySet()){
				List<Record> columnList = Constant.pluginTable.get(tableName);
				rs = stm.executeQuery("select * from information_schema.TABLES where TABLE_SCHEMA='"+Constant.dbName+"' and TABLE_NAME='"+tableName+"'");
				if(!rs.next()){
					addTable(tableName, columnList, stm);
					log.info(tableName+"创建完毕！");
					continue;
				}
				rs = stm.executeQuery("select COLUMN_NAME as name from information_schema.COLUMNS where TABLE_SCHEMA='"+Constant.dbName+"' and TABLE_NAME='"+tableName+"' AND COLUMN_NAME not in ('id','create_time','update_time')");
				List<Record> dbColumnList = new ArrayList<Record>();
				while (rs.next()) {
					Record dbColumn = new Record();
					dbColumn.set("name", rs.getString("name"));
					dbColumnList.add(dbColumn);					
				}
				List<Record> addList = new ArrayList<Record>();
				List<Record> dropList = new ArrayList<Record>();
				for(int i=0;i<columnList.size();i++){
					boolean is_have = false;
					for(int j=0;j<dbColumnList.size();j++){
						if(columnList.get(i).getStr("name").toLowerCase().equals(dbColumnList.get(j).getStr("name").toLowerCase())){
							is_have = true;
							break;
						}
					}
					if(!is_have){
						addList.add(columnList.get(i));
					}
				}
				for(int i=0;i<dbColumnList.size();i++){
					boolean is_have = false;
					for(int j=0;j<columnList.size();j++){
						if(dbColumnList.get(i).getStr("name").toLowerCase().equals(columnList.get(j).getStr("name").toLowerCase())){
							is_have = true;
							break;
						}
					}
					if(!is_have){
						dropList.add(dbColumnList.get(i));
					}
				}
				if(addList.size()>0||dropList.size()>0){
					if(Constant.updateTable){
						alertTable(tableName, addList, dropList,stm);
					}else {
						log.error(tableName+"表结构有变动，请及时变更！");
					}
				}
			}
		}catch (Exception e){
			e.printStackTrace();
			log.info("数据表构建失败，请检查建表数组！");
			throw new RuntimeException();
		}finally {
			try{
				if(rs!=null) rs.close();
				if(stm!=null) stm.close();
			}catch (Exception e) {}
		}
	}
	public static void addTable(String tableName,List<Record> columnList,Statement stm){
		StringBuilder sql = new StringBuilder();
		sql.append("CREATE TABLE `"+tableName+"` (");
		sql.append("`id` int(11) NOT NULL AUTO_INCREMENT,");
		Record column = null;
		for(int i=0;i<columnList.size();i++){
			column = columnList.get(i);
			sql.append("`"+column.getStr("name")+"`");
			if(column.getStr("length").equals("")){
				sql.append(" "+column.getStr("type"));
			}else {
				sql.append(" "+column.getStr("type")+"("+column.getStr("length")+")");
			}
			if(column.get("default")!=null){
				if(column.getStr("type").equals("varchar")){
					sql.append(" NOT NULL DEFAULT '"+column.getStr("default")+"'");
				}else {
					sql.append(" NOT NULL DEFAULT "+column.getStr("default"));
				}
			}
			sql.append(",");
		}
		sql.append("`update_time` datetime,");  
		sql.append("`create_time` timestamp DEFAULT CURRENT_TIMESTAMP,");  
		sql.append("PRIMARY KEY (`id`)");  
		sql.append(") ENGINE=MyISAM DEFAULT CHARSET=utf8");
		try{
			stm.execute(sql.toString());
		}catch (Exception e) {
			log.error(tableName+"创建失败！");
			System.out.println(sql.toString());
			e.printStackTrace();
		}
		
	}
	
	public static void alertTable(String tableName,List<Record> addList,List<Record> dropList,Statement stm){
		StringBuilder sql = new StringBuilder();
		sql.append("ALTER TABLE `"+tableName+"`");
		for(int i=0;i<dropList.size();i++){
			sql.append("DROP COLUMN `"+dropList.get(i).getStr("name")+"`,");
		}
		for(int i=0;i<addList.size();i++){
			sql.append("ADD COLUMN `"+addList.get(i).getStr("name")+"`");
			if(addList.get(i).getStr("length").equals("")){
				sql.append(" "+addList.get(i).getStr("type"));
			}else {
				sql.append(" "+addList.get(i).getStr("type")+"("+addList.get(i).getStr("length")+")");
			}
			if(addList.get(i).get("default")!=null){
				if(addList.get(i).getStr("type").equals("int")){
					sql.append(" NOT NULL DEFAULT "+addList.get(i).getStr("default"));
				}else {
					sql.append(" NOT NULL DEFAULT '"+addList.get(i).getStr("default")+"'");
				}
			}
			sql.append(",");
		}
		try{
			stm.execute(sql.substring(0,sql.length()-1));
			log.warn(tableName+"表结构更新成功！");
		}catch (Exception e) {
			log.error(tableName+"表结构更新失败！");
			System.out.println(sql.substring(0,sql.length()-1));
			e.printStackTrace();
		}
	}
}
